package com.xhu.collegestudentrentalsystem.module.user.dao;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xhu.collegestudentrentalsystem.module.user.entity.House;
import com.xhu.collegestudentrentalsystem.module.user.entity.dto.HouseDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @author zhu huilin
 * @date 2023/2/21 20:41
 */
public interface HouseDAO extends BaseMapper<House> {

    /**
     * 修改房子指定字段的信息
     */
    @Update("UPDATE house " +
            " SET rent_type=#{rentType}, " +
            " title = #{title}, " +
            " content=#{content}, " +
            " city=#{city}, " +
            " address=#{address}, " +
            " thumbnail_url=#{thumbnailUrl}, " +
            " slide_url=#{slideUrl}, " +
            " month_rent=#{monthRent}, " +
            " certificate_no=#{certificateNo}, " +
            " toilet_num=#{toiletNum}, " +
            " kichen_num=#{kichenNum}, " +
            " living_room_num=#{livingRoomNum}, " +
            " bedroom_num=#{bedroomNum}, " +
            " has_air_conditioner=#{hasAirConditioner}, " +
            " `area`=#{area}, " +
            " `floor`=#{floor}, " +
            " max_floor=#{maxFloor}, " +
            " has_elevator=#{hasElevator}, " +
            " build_year=#{buildYear}, " +
            " direction=#{direction}, " +
            " longitude_latitude=#{longitudeLatitude}, " +
            " contact_name=#{contactName}, " +
            " contact_phone=#{contactPhone} " +
            " WHERE id = #{id}")
    public Integer updateInfoAndPic(House house);

    @Update("UPDATE house " +
            " SET rent_type=#{rentType}, " +
            " title = #{title}, " +
            " content=#{content}, " +
            " city=#{city}, " +
            " address=#{address}, " +
            " month_rent=#{monthRent}, " +
            " certificate_no=#{certificateNo}, " +
            " toilet_num=#{toiletNum}, " +
            " kichen_num=#{kichenNum}, " +
            " living_room_num=#{livingRoomNum}, " +
            " bedroom_num=#{bedroomNum}, " +
            " has_air_conditioner=#{hasAirConditioner}, " +
            " `area`=#{area}, " +
            " `floor`=#{floor}, " +
            " max_floor=#{maxFloor}, " +
            " has_elevator=#{hasElevator}, " +
            " build_year=#{buildYear}, " +
            " direction=#{direction}, " +
            " longitude_latitude=#{longitudeLatitude}, " +
            " contact_name=#{contactName}, " +
            " contact_phone=#{contactPhone} " +
            " WHERE id = #{id}")
    public Integer updateInfo(House house);

    /**
     * 根据id值修改房子的状态
     */
    @Update("update house set status=#{status} where id=#{houseId}")
    public Integer updateHouseStatus(@Param("houseId") Long houseId,@Param("status") Integer status);

    /**
     * 查询最新推荐房子信息（首页），只查询6条记录即可
     */
    @Select("SELECT house.*, AVG(score.overall) score FROM house " +
            " LEFT JOIN (SELECT house_id, overall FROM house_score, house_order WHERE house_score.order_id = house_order.id) score " +
            " ON house_id = id " +
            " WHERE `status` = 0 OR `status` = 1 GROUP BY id ORDER BY `status`,create_time DESC LIMIT 0,6 ")
    public List<House> findHouseOrderByStatusAndTime();

    /**
     * 查询高分优选房子信息（首页），只查询6条记录即可
     */
    @Select("SELECT house.*, AVG(score.overall) score FROM house " +
            " LEFT JOIN (SELECT house_id, overall FROM house_score, house_order WHERE house_score.order_id = house_order.id) score " +
            " ON house_id = id " +
            " WHERE `status` = 0 OR `status` = 1 GROUP BY id ORDER BY `status`,${orderType} DESC LIMIT 0,6 ")
    public List<House> findHouseOrderByStatusAndParam( @Param("orderType") String orderType);

    /**
     * 根据传入字段查询房子信息，并根据条件排序
     */
    @Select("SELECT house.*, AVG(score.overall) score, COUNT(score.overall) total FROM house " +
            " LEFT JOIN (SELECT house_id, overall FROM house_score, house_order WHERE house_score.order_id = house_order.id) score " +
            " ON house_id = id ${ew.customSqlSegment} " +
            " GROUP BY house.id ORDER BY ${orderType} DESC, status")
    public List<House> searchHousesByDto(@Param("ew") QueryWrapper<House> queryWrapper, @Param("orderType") String orderType);
}
